{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "12af070a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ['JAVA_HOME'] = \"D:\\\\Develop\\\\Java\\\\jdk1.8.0_241\" \n",
    "os.environ['PYSPARK_PYTHON']=\"C:\\\\Users\\\\Spencer Cheung\\\\.conda\\\\envs\\\\py37\\\\python.exe\"#本机电脑所使用的python编译器的地址"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d259b46e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Spencer Cheung\\.conda\\envs\\py37\\lib\\site-packages\\pyspark\\context.py:317: FutureWarning: Python 3.7 support is deprecated in Spark 3.4.\n",
      "  warnings.warn(\"Python 3.7 support is deprecated in Spark 3.4.\", FutureWarning)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://LAPTOP-METNM36M:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.4.4</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>test</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        "
      ],
      "text/plain": [
       "<SparkContext master=local appName=test>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.context import SparkContext\n",
    "from pyspark.sql import SparkSession\n",
    "sc=SparkContext('local','test')\n",
    "spark=SparkSession(sc)\n",
    "sc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6f728b88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['street,city,zip,state,beds,baths,sq__ft,type,sale_date,price,latitude,longitude',\n",
       " '3526 HIGH ST,SACRAMENTO,95838,CA,2,1,836,Residential,Wed May 21 00:00:00 EDT 2008,59222,38.631913,-121.434879',\n",
       " '51 OMAHA CT,SACRAMENTO,95823,CA,3,1,1167,Residential,Wed May 21 00:00:00 EDT 2008,68212,38.478902,-121.431028',\n",
       " '2796 BRANCH ST,SACRAMENTO,95815,CA,2,1,796,Residential,Wed May 21 00:00:00 EDT 2008,68880,38.618305,-121.443839',\n",
       " '2805 JANETTE WAY,SACRAMENTO,95815,CA,2,1,852,Residential,Wed May 21 00:00:00 EDT 2008,69307,38.616835,-121.439146']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.types import *\n",
    "from pyspark.sql import Row\n",
    "houses_data = spark.read.format('org.apache.spark.sql.execution.datasources.csv.CSVFileFormat')\\\n",
    ".option('header','true')\\\n",
    ".option('inferSchema','true')\\\n",
    ".load('data/houses_data.csv')\n",
    "rdd = sc.textFile('data/houses_data.csv')\n",
    "rdd.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c1e3c69",
   "metadata": {},
   "source": [
    "1.对数据集的每一行用逗号进行分隔。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9546999c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['street',\n",
       "  'city',\n",
       "  'zip',\n",
       "  'state',\n",
       "  'beds',\n",
       "  'baths',\n",
       "  'sq__ft',\n",
       "  'type',\n",
       "  'sale_date',\n",
       "  'price',\n",
       "  'latitude',\n",
       "  'longitude'],\n",
       " ['3526 HIGH ST',\n",
       "  'SACRAMENTO',\n",
       "  '95838',\n",
       "  'CA',\n",
       "  '2',\n",
       "  '1',\n",
       "  '836',\n",
       "  'Residential',\n",
       "  'Wed May 21 00:00:00 EDT 2008',\n",
       "  '59222',\n",
       "  '38.631913',\n",
       "  '-121.434879']]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#由考生填写\n",
    "rdd = rdd.map(lambda line: line.split(\",\"))\n",
    "#由考生填写\n",
    "rdd.take(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74b78783",
   "metadata": {},
   "source": [
    "2.使用“fiter”删除包含标题的行。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "19534c73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['3526 HIGH ST',\n",
       "  'SACRAMENTO',\n",
       "  '95838',\n",
       "  'CA',\n",
       "  '2',\n",
       "  '1',\n",
       "  '836',\n",
       "  'Residential',\n",
       "  'Wed May 21 00:00:00 EDT 2008',\n",
       "  '59222',\n",
       "  '38.631913',\n",
       "  '-121.434879'],\n",
       " ['51 OMAHA CT',\n",
       "  'SACRAMENTO',\n",
       "  '95823',\n",
       "  'CA',\n",
       "  '3',\n",
       "  '1',\n",
       "  '1167',\n",
       "  'Residential',\n",
       "  'Wed May 21 00:00:00 EDT 2008',\n",
       "  '68212',\n",
       "  '38.478902',\n",
       "  '-121.431028']]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header = rdd.first()\n",
    "#由考生填写\n",
    "rdd = rdd.filter(lambda line: line != header)\n",
    "#由考生填写\n",
    "rdd.take(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "309d21b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------+-----+----+-----+----+------+\n",
      "|              street|          city|  zip|beds|baths|sqft| price|\n",
      "+--------------------+--------------+-----+----+-----+----+------+\n",
      "|        3526 HIGH ST|    SACRAMENTO|95838|   2|    1| 836| 59222|\n",
      "|         51 OMAHA CT|    SACRAMENTO|95823|   3|    1|1167| 68212|\n",
      "|      2796 BRANCH ST|    SACRAMENTO|95815|   2|    1| 796| 68880|\n",
      "|    2805 JANETTE WAY|    SACRAMENTO|95815|   2|    1| 852| 69307|\n",
      "|     6001 MCMAHON DR|    SACRAMENTO|95824|   2|    1| 797| 81900|\n",
      "|  5828 PEPPERMILL CT|    SACRAMENTO|95841|   3|    1|1122| 89921|\n",
      "| 6048 OGDEN NASH WAY|    SACRAMENTO|95842|   3|    2|1104| 90895|\n",
      "|       2561 19TH AVE|    SACRAMENTO|95820|   3|    1|1177| 91002|\n",
      "|11150 TRINITY RIV...|RANCHO CORDOVA|95670|   2|    2| 941| 94905|\n",
      "|        7325 10TH ST|     RIO LINDA|95673|   3|    2|1146| 98937|\n",
      "|    645 MORRISON AVE|    SACRAMENTO|95838|   3|    2| 909|100309|\n",
      "|       4085 FAWN CIR|    SACRAMENTO|95823|   3|    2|1289|106250|\n",
      "|     2930 LA ROSA RD|    SACRAMENTO|95815|   1|    1| 871|106852|\n",
      "|       2113 KIRK WAY|    SACRAMENTO|95822|   3|    1|1020|107502|\n",
      "| 4533 LOCH HAVEN WAY|    SACRAMENTO|95842|   2|    2|1022|108750|\n",
      "|      7340 HAMDEN PL|    SACRAMENTO|95842|   2|    2|1134|110700|\n",
      "|         6715 6TH ST|     RIO LINDA|95673|   2|    1| 844|113263|\n",
      "|6236 LONGFORD DR ...|CITRUS HEIGHTS|95621|   2|    1| 795|116250|\n",
      "|     250 PERALTA AVE|    SACRAMENTO|95833|   2|    1| 588|120000|\n",
      "|     113 LEEWILL AVE|     RIO LINDA|95673|   3|    2|1356|121630|\n",
      "+--------------------+--------------+-----+----+-----+----+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>zip</th>\n",
       "      <th>beds</th>\n",
       "      <th>baths</th>\n",
       "      <th>sqft</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3526 HIGH ST</td>\n",
       "      <td>SACRAMENTO</td>\n",
       "      <td>95838</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>836</td>\n",
       "      <td>59222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51 OMAHA CT</td>\n",
       "      <td>SACRAMENTO</td>\n",
       "      <td>95823</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1167</td>\n",
       "      <td>68212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2796 BRANCH ST</td>\n",
       "      <td>SACRAMENTO</td>\n",
       "      <td>95815</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>796</td>\n",
       "      <td>68880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2805 JANETTE WAY</td>\n",
       "      <td>SACRAMENTO</td>\n",
       "      <td>95815</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>852</td>\n",
       "      <td>69307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6001 MCMAHON DR</td>\n",
       "      <td>SACRAMENTO</td>\n",
       "      <td>95824</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>797</td>\n",
       "      <td>81900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             street        city    zip beds baths  sqft  price\n",
       "0      3526 HIGH ST  SACRAMENTO  95838    2     1   836  59222\n",
       "1       51 OMAHA CT  SACRAMENTO  95823    3     1  1167  68212\n",
       "2    2796 BRANCH ST  SACRAMENTO  95815    2     1   796  68880\n",
       "3  2805 JANETTE WAY  SACRAMENTO  95815    2     1   852  69307\n",
       "4   6001 MCMAHON DR  SACRAMENTO  95824    2     1   797  81900"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = rdd.map(lambda line: Row(street = line[0], city = line[1], zip=line[2], beds=line[4], baths=line[5],sqft=line[6], price=line[9])).toDF()\n",
    "df.show()\n",
    "df.toPandas().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5036f213",
   "metadata": {},
   "source": [
    "3.df按照“beds”字段分组，并计算每个分组中记录的数量，并显示结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "32265f15",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+\n",
      "|beds|count|\n",
      "+----+-----+\n",
      "|   3|  413|\n",
      "|   8|    1|\n",
      "|   0|  108|\n",
      "|   5|   59|\n",
      "|   6|    3|\n",
      "|   1|   10|\n",
      "|   4|  258|\n",
      "|   2|  133|\n",
      "+----+-----+\n",
      "\n",
      "+-------+------------------+------------------+------------------+------------------+\n",
      "|summary|             baths|              beds|             price|              sqft|\n",
      "+-------+------------------+------------------+------------------+------------------+\n",
      "|  count|               985|               985|               985|               985|\n",
      "|   mean|1.7766497461928934|2.9116751269035532|234144.26395939087|1314.9167512690356|\n",
      "| stddev|0.8953714223186463|1.3079322320435807|138365.83908492787| 853.0482425034448|\n",
      "|    min|                 0|                 0|            100000|                 0|\n",
      "|    max|                 5|                 8|             99000|               998|\n",
      "+-------+------------------+------------------+------------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#由考生填写 \n",
    "df.groupBy('beds').count().show()\n",
    "#由考生填写\n",
    "df.describe(['baths','beds','price','sqft']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1e41ee4d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+------------------+------------------+------------------+\n",
      "|summary|             baths|              beds|             price|              sqft|\n",
      "+-------+------------------+------------------+------------------+------------------+\n",
      "|  count|               814|               814|               814|               814|\n",
      "|   mean|1.9606879606879606|3.2444717444717446| 229448.3697788698|1591.1461916461917|\n",
      "| stddev|0.6698038253879438|0.8521372615281972|119825.57606009027| 663.8419297942895|\n",
      "|    min|                 1|                 1|            100000|              1000|\n",
      "|    max|                 5|                 8|             99000|               998|\n",
      "+-------+------------------+------------------+------------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import pyspark.mllib\n",
    "import pyspark.mllib.regression\n",
    "from pyspark.mllib.regression import LabeledPoint\n",
    "from pyspark.sql.functions import *\n",
    "df = df.select('price','baths','beds','sqft')\n",
    "df = df[df.baths > 0]\n",
    "df = df[df.beds > 0]\n",
    "df = df[df.sqft >0]\n",
    "df.describe(['baths','beds','price','sqft']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4b9cf14d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[LabeledPoint(59222.0, [1.0,2.0,836.0]),\n",
       " LabeledPoint(68212.0, [1.0,3.0,1167.0]),\n",
       " LabeledPoint(68880.0, [1.0,2.0,796.0]),\n",
       " LabeledPoint(69307.0, [1.0,2.0,852.0]),\n",
       " LabeledPoint(81900.0, [1.0,2.0,797.0])]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = df.rdd.map(lambda line:LabeledPoint(line[0],[line[1:]]))\n",
    "temp.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "02307f22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('1', '2', '836'),\n",
       " ('1', '3', '1167'),\n",
       " ('1', '2', '796'),\n",
       " ('1', '2', '852'),\n",
       " ('1', '2', '797')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.mllib.util import MLUtils\n",
    "from pyspark.mllib.linalg import Vectors\n",
    "from pyspark.mllib.feature import StandardScaler\n",
    "features = df.rdd.map(lambda row: row[1:])\n",
    "features.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cecbd910",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['59222', '68212', '68880', '69307', '81900']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "standardizer = StandardScaler()\n",
    "model = standardizer.fit(features)\n",
    "features_transform = model.transform(features)\n",
    "features_transform.take(5)\n",
    "lab = df.rdd.map(lambda row: row[0])\n",
    "lab.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba395544",
   "metadata": {},
   "source": [
    "4.将标签lab和特征features transform进行zip操作。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2a0efb82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('59222', DenseVector([1.493, 2.347, 1.2593])),\n",
       " ('68212', DenseVector([1.493, 3.5206, 1.7579])),\n",
       " ('68880', DenseVector([1.493, 2.347, 1.1991])),\n",
       " ('69307', DenseVector([1.493, 2.347, 1.2834])),\n",
       " ('81900', DenseVector([1.493, 2.347, 1.2006]))]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#由考生填写 \n",
    "transformedData = lab.zip(features_transform)\n",
    "#由考生填写 \n",
    "transformedData.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "835f0698",
   "metadata": {},
   "source": [
    "5.将transformedData转换为LabeledPoint类型,其中row[0]作为标签,row[1]作为特征向量。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a379a0f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Spencer Cheung\\.conda\\envs\\py37\\lib\\site-packages\\pyspark\\mllib\\regression.py:365: FutureWarning: Deprecated in 2.0.0. Use ml.regression.LinearRegression.\n",
      "  warnings.warn(\"Deprecated in 2.0.0. Use ml.regression.LinearRegression.\", FutureWarning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "161670.2532374391"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#由考生填写\n",
    "transformedData = transformedData.map(lambda row: LabeledPoint(row[0],[row[1]]))\n",
    "#由考生填写\n",
    "transformedData.take(5)\n",
    "traingingData,testingData = transformedData.randomSplit([.8,.2],seed=1234)\n",
    "from pyspark.mllib.regression import LinearRegressionWithSGD\n",
    "linearModel = LinearRegressionWithSGD.train(traingingData,1000,.2)\n",
    "linearModel.weights\n",
    "testingData.take(10)\n",
    "linearModel.predict([1.49297445326,3.52055958053,1.73535287287])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebe04e05",
   "metadata": {},
   "outputs": [],
   "source": [
    "#--------------------正式代码结束，以下为中间结果打印------------------------###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e92f29bc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[LabeledPoint(59222.0, [1.4929744532599674,2.3470397203535733,1.2593359389923722]),\n",
       " LabeledPoint(68212.0, [1.4929744532599674,3.52055958053036,1.7579486134020315]),\n",
       " LabeledPoint(68880.0, [1.4929744532599674,2.3470397203535733,1.1990806309066127]),\n",
       " LabeledPoint(69307.0, [1.4929744532599674,2.3470397203535733,1.283438062226676]),\n",
       " LabeledPoint(81900.0, [1.4929744532599674,2.3470397203535733,1.2005870136087566])]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transformedData.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a947bfd4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[LabeledPoint(100309.0, [2.9859489065199347,3.52055958053036,1.369301876248883]),\n",
       " LabeledPoint(124100.0, [2.9859489065199347,3.52055958053036,2.4117187061325214]),\n",
       " LabeledPoint(148750.0, [2.9859489065199347,4.694079440707147,2.2173953375559474]),\n",
       " LabeledPoint(150000.0, [1.4929744532599674,1.1735198601767867,1.1448508536294293]),\n",
       " LabeledPoint(161500.0, [2.9859489065199347,4.694079440707147,2.3906293483025056]),\n",
       " LabeledPoint(166357.0, [1.4929744532599674,4.694079440707147,2.9449781826914925]),\n",
       " LabeledPoint(168000.0, [2.9859489065199347,3.52055958053036,2.224927251066667]),\n",
       " LabeledPoint(178480.0, [2.9859489065199347,3.52055958053036,1.7850635020406234]),\n",
       " LabeledPoint(181872.0, [1.4929744532599674,3.52055958053036,1.7353528728698717]),\n",
       " LabeledPoint(182587.0, [4.478923359779902,4.694079440707147,2.788314381668518])]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testingData.take(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27530c2e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
